<?php

// +----------------------------------------------------------------------
// | ShopXO 国内领先企业级B2C免费开源电商系统
// +----------------------------------------------------------------------
// | Copyright (c) 2011~2019 http://shopxo.net All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: juzi
// +----------------------------------------------------------------------

namespace base;
use phpexcel\PHPExcel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;

/**
 * Excel驱动.
 *
 * @author   juzi
 * @blog    https://i.csdn.net/#/uc/profile
 *
 * @version  0.0.1
 * @datetime 2020年5月17日16:03:09T21:51:08+0800
 */
class Excel
{
    private $filename;
    private $file_type;
    private $suffix;
    private $data;
    private $title;
    private $jump_url;
    private $msg;
    private $horizontal_center;
    private $vertical_center;
    private $warap_text;

    /**
     * [__construct 构造方法].
     *
     * @author   juzi
     * @blog    https://i.csdn.net/#/uc/profile
     *
     * @version  0.0.1
     * @datetime 2020年5月17日16:03:09T15:09:17+0800
     *
     * @param [string] $params['filename']          [文件名称（追加当前时间）]
     * @param [string] $params['suffix']            [文件后缀名（默认xls）]
     * @param [string] $params['jump_url']          [出错跳转url地址（默认上一个页面）]
     * @param [string] $params['msg']               [错误提示信息]
     * @param [string] $params['file_type']         [导出文件类型（默认excel）]
     * @param [array]  $params['title']             [标题（二维数组）]
     * @param [array]  $params['data']              [数据（二维数组）]
     * @param [int]    $params['horizontal_center'] [是否水平居中 1是]
     * @param [int]    $params['vertical_center']   [是否垂直居中 1是]
     * @param [int]    $params['warap_text']        [是否内容自动换行 1是]
     */
    public function __construct($params = [])
    {
        // 文件名称
        $date = date('YmdHis');
        $this->filename = isset($params['filename']) ? $params['filename'].'-'.$date : $date;

        // 文件类型, 默认excel
        $type_all = ['excel' => 'vnd.ms-excel', 'pdf' => 'pdf'];
        $this->file_type = (isset($params['file_type']) && isset($type_all[$params['file_type']])) ? $type_all[$params['file_type']] : $type_all['excel'];

        // 文件后缀名称
        $this->suffix = empty($params['suffix']) ? 'xls' : $params['suffix'];

        // 标题
        $this->title = isset($params['title']) ? $params['title'] : [];

        // 数据
        $this->data = isset($params['data']) ? $params['data'] : [];

        // 出错跳转地址
        $this->jump_url = empty($params['jump_url']) ? (empty($_SERVER['HTTP_REFERER']) ? __MY_URL__ : $_SERVER['HTTP_REFERER']) : $params['jump_url'];

        // 错误提示信息
        $this->msg = empty($params['msg']) ? 'title or data cannot be empty!' : $params['msg'];

        // 水平,垂直居中
        $this->horizontal_center = isset($params['horizontal_center']) ? intval($params['horizontal_center']) : 1;
        $this->vertical_center = isset($params['vertical_center']) ? intval($params['vertical_center']) : 1;

        // 内容自动换行
        $this->warap_text = isset($params['warap_text']) ? intval($params['warap_text']) : 1;

        // 引入PHPExcel类库
        //require ROOT_PATH.'../extend'.DS.'phpexcel'.DS.'PHPExcel.php';
    }

    /**
     * [Export excel文件导出].
     *
     * @author   juzi
     * @blog    https://i.csdn.net/#/uc/profile
     *
     * @version  0.0.1
     * @datetime 2020年5月17日16:03:09T15:12:01+0800
     */
    public function Export(){
        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet()->setTitle('云盟商桥用户订单');
         # 设置sheet的名字  两种方法

        #合并第一行
        $title=['收货人','手机号','地址','订单时间','订单金额','商品名称','商品编号','商品规格','商品数量','商品单价','合计金额',];
        $data = [
            [
                'title_A' => $this->data['receive_name'],
                'title_B' => $this->data['receive_phone'],
                'title_C' => $this->data['address_all'],
                'title_D' => $this->data['pay_time'],
                'title_E' =>$this->data['total_price'],
                'title_F' =>$this->data['bizpro']['bizpro_name'],
                'title_G' => $this->data['bizpro']['bizpro_number'],
                'title_H' => $this->data['bizpro_spec_value'],
                'title_I' => $this->data['bizpro']['buy_num'],
                'title_J' => $this->data['bizpro']['price'],
                'title_K' => $this->data['bizpro']['mod_total_price']
        ],];

        // Create new Spreadsheet object


        // 使用 setCellValueByColumnAndRow

        //设置单元格内容
        //设置表头
        foreach ($title as $key => $value)
        {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        $row = 2; // 从第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }


        //设定样式
        //所有sheet的表头样式 加粗
        $font = [
            'font' => [
                'bold' => true,
            ],
        ];
        //所有sheet的内容样式 加黑色边框
        $borders = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'black'],
                ],
            ],
        ];
        $sheet->getStyle('A1:K1')->applyFromArray($font);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(101);
        $sheet->getColumnDimension('F')->setWidth(25);
        $sheet->getColumnDimension('G')->setWidth(25);



        $sheet->getStyle('A1:K' . ($row - 1))->applyFromArray($borders);


        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="云盟商桥用户订单.xlsx"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    public function Exporttest(){


        $spreadsheet = new Spreadsheet();


        $sheet = $spreadsheet->getActiveSheet()->setTitle($this->filename);
      //  dump($this->filename);exit;
        # 设置sheet的名字  两种方法

        #合并第一行
        $title=[];
        $title_all=$this->title;
        foreach($title_all as $k=>$v)
        {
            $title[$k]=$title_all[$k]['name'];

        }
        $cell = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        $data=[];
        $data_all=$this->data;

        foreach ($data_all as $k=>$v)
        {
            $data_val=[];
            foreach ($title_all as $kk=>$vv)
            {
            $data_val['title_'.$cell[$kk]] =$v[$vv['type']];
            }
            $data[$k]=$data_val;
            //var_($data_val);
            //var_($data_val);
        }


        //设置单元格内容
        //设置表头
        foreach ($title as $key => $value)
        {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        $row = 2;
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }


        //设定样式
        //所有sheet的表头样式 加粗
        $font = [
            'font' => [
                'bold' => true,
            ],
        ];
        //所有sheet的内容样式 加黑色边框
        $borders = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'black'],
                ],
            ],
        ];

        $sheet->getStyle('A1:'.$cell[count($data[0])-1].'1')->applyFromArray($font);
        for($i=0;$i<=count($data[0])-1;$i++){

            $sheet->getColumnDimension($cell[$i])->setWidth(30);
        }

        $sheet->getStyle('A1:'.$cell[count($data[0])-1] . ($row - 1))->applyFromArray($borders);


        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$this->filename.'.xlsx"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }


    public function Exporta()
    {
        // 是否有数据
        if (empty($this->title) && empty($this->data)) {
            echo '<script>alert("'.$this->msg.'");</script>';
            echo '<script>window.location.href="'.$this->jump_url.'"</script>';
            die;
        }

        // excel对象
        $excel = new PHPExcel();

        // 操作第一个工作表
        $excel->setActiveSheetIndex(0);

        // 文件输出类型
        switch ($this->file_type) {
            // PDF
            case 'pdf':
                $writer = PHPExcel_IOFactory::createWriter($excel, 'PDF');
                $writer->setSheetIndex(0);
                break;

            // 默认EXCEL
            default:
                $writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
        }

        // 获取配置编码类型
        $excel_charset =0;
        $charset = 'utf-8';

//        // 水平,垂直居中
        if (1 == $this->horizontal_center) {
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        if (1 == $this->vertical_center) {
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        }

        //设置自动换行
        if (1 == $this->warap_text) {
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
        }
        $last = count($this->title) - 1;

        $cell = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
        $excel->getActiveSheet()->mergeCells('A1:'.$cell[$last].'1');
        $excel->setActiveSheetIndex(0)->setCellValue('A1', $this->filename);
        // 标题
        $temp_key = 0;
        foreach ($this->title as $k => $v) {
            $col = \PHPExcel_Cell::stringFromColumnIndex($temp_key).'2';

            $excel->getActiveSheet()->setCellValue($col, (0 == $excel_charset) ? $v['name'] : iconv('utf-8', $charset, $v['name']));
            ++$temp_key;
        }

        // 内容

        foreach ($this->data as $k => $v) {
            $i = $k + 3;

            if (is_array($v) && !empty($v)) {
                $temp_key = 0;
                foreach ($this->title as $tk => $tv) {
                    $height = isset($tv['height']) ? intval($tv['height']) : 0;
                    $width = isset($tv['width']) ? intval($tv['width']) : $height;
                    $col = \PHPExcel_Cell::stringFromColumnIndex($temp_key);
                    if ('images' == $tv['type']) {
                        $drawing = new \PHPExcel_Worksheet_Drawing();
                        $drawing->setPath($v[$tk]);

                        // 设置宽度高度
                        $number = empty($height) ? 50 : $height - 10;
                        $drawing->setHeight($number);
                        $drawing->setWidth($number);
                        $drawing->setCoordinates($col.$i);

                        // 图片偏移距离
                        $x = ($width > 0) ? (($width - $number) / 2) + 15 : 15;
                        $drawing->setOffsetX($x);
                        $drawing->setOffsetY(15);
                        $drawing->setWorksheet($excel->getActiveSheet());
                    } else {
                        $excel->getActiveSheet()->setCellValueExplicit($col.$i, (0 == $excel_charset) ? $v[$tk] : iconv('utf-8', $charset, $v[$tk]), \PHPExcel_Cell_DataType::TYPE_STRING);
                    }

                    // 单元格宽高
                    if ($width > 0) {
                        $excel->getActiveSheet()->getColumnDimension($col)->setWidth($width / 5);
                    }
                    if ($height > 0) {
                        $excel->getActiveSheet()->getRowDimension($i)->setRowHeight($height);
                    }
                    ++$temp_key;
                }
            }
        }

        // 头部
//        header('Pragma: public');
//        header('Expires: 0');
//        header('Cache-Control:must-revalidate, post-check=0, pre-check=0');
//        header('Content-Type:application/force-download');
//        header('Content-Type: application/'.$this->file_type.';;charset='.$charset);
//        header('Content-Type:application/octet-stream');
//        header('Content-Type:application/download');
//        header('Content-Disposition:attachment;filename='.$this->filename.'.'.$this->suffix);
//        header('Content-Transfer-Encoding:binary');

        $filename = '审批数据.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = \PHPExcel_IOFactory::createWriter($excel, 'Xlsx');



        $writer->save('php://output');
    }

    /**
     * [Import excel文件导入].
     * @author   juzi
     * @blog    https://i.csdn.net/#/uc/profile
     * @version  0.0.1
     * @datetime 2017-04-06T18:18:55+0800
     * @param [string] $file [文件位置,空则读取全局excel的临时文件]
     * @return [array] [数据列表]
     */
    public function Import($file = '')
    {
        // 文件为空则取全局文变量excel的临时文件
        if (empty($file) && !empty($_FILES['excel']['tmp_name'])) {
            $file = $_FILES['excel']['tmp_name'];
        }

        // 文件地址是否有误,title数据是否有数据
        if (empty($file) || empty($this->title)) {
            echo '<script>alert("'.$this->msg.'");</script>';
            echo '<script>window.location.href="'.$this->jump_url.'"</script>';
            die;
        }

        // 取得文件基础数据
        $reader = \PHPExcel_IOFactory::createReader('Excel5');
        $excel = $reader->load($file);

        // 取得总行数
        $worksheet = $excel->getActiveSheet();

        // 取得总列数
        $highest_row = $worksheet->getHighestRow();

        // 取得最高的列
        $highest_column = $worksheet->getHighestColumn();

        // 总列数
        $highest_column_index = \PHPExcel_Cell::columnIndexFromString($highest_column);

        // 定义变量
        $result = [];
        $field = [];

        // 读取数据
        for ($row = 1; $row <= $highest_row; ++$row) {
            // 临时数据
            $info = [];

            // 注意 highest_column_index 的列数索引从0开始
            for ($col = 0; $col < $highest_column_index; ++$col) {
                $value = $worksheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
                if (1 == $row) {
                    foreach ($this->title as $tk => $tv) {
                        if ($value == $tv['name']) {
                            $tv['field'] = $tk;
                            $field[$col] = $tv;
                        }
                    }
                } else {
                    if (!empty($field)) {
                        $info[$field[$col]['field']] = ('int' == $field[$col]['type']) ? trim(ScienceNumToString($value)) : trim($value);
                    }
                }
            }
            if ($row > 1) {
                $result[] = $info;
            }
        }

        return $result;
    }
}
